import uuid
import datetime
from miaoyun.model.base import Base
from sqlalchemy import Column, String, DateTime, UniqueConstraint, Integer, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import JSONB


def auto_id():
    return uuid.uuid4().hex


class DashboardPanel(Base):
    """关联"""
    __tablename__ = 'dashboard_panel'
    dashboard_id = Column(String, ForeignKey('dashboard.id'), primary_key=True, comment='仪表板ID')
    panel_id = Column(String, ForeignKey('panel.id'), primary_key=True, comment='仪表盘ID')


class Panel(Base):
    """库面板"""
    __tablename__ = 'panel'
    id = Column(String, primary_key=True, default=auto_id, comment='主键ID')

    title = Column(String, comment='库面板名称')
    description = Column(String, nullable=False, comment='描述信息')

    source = Column(JSONB, default=None, comment='数据源 默认elasticsearch')
    search = Column(JSONB, default={}, comment='搜索条件')
    chart = Column(JSONB, default={}, comment='图表类型 + 图表显示配置')
    cron = Column(JSONB, default=None, comment='定时任务配置')

    created_by = Column(String, default=None, comment='创建人')
    created_app = Column(String, default=None, comment='创建app')
    created_at = Column(DateTime, default=datetime.datetime.utcnow, comment='创建时间')
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow, comment='更新时间')
    mode_rw = Column(Integer, default=0, comment='读写模式 不公开：0 公共读：1 公共读写：2')

    __table_args__ = (UniqueConstraint('created_app', 'title'),)


class Dashboard(Base):
    """仪表板"""
    __tablename__ = 'dashboard'
    id = Column(String, primary_key=True, default=auto_id, comment='主键ID')

    title = Column(String, nullable=False, comment='仪表板名称')
    description = Column(String, default='', comment='描述信息')
    tags = Column(JSONB, default=[], comment='标签')

    panels = Column(JSONB, default=[], comment='图表')
    variables = Column(JSONB, default={}, comment='变量模板')

    created_by = Column(String, default=None, comment='创建人')
    created_app = Column(String, default=None, comment='创建App')
    created_at = Column(DateTime, default=datetime.datetime.utcnow, comment='创建时间')
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow, comment='更新时间')
    mode_rw = Column(Integer, default=0, comment='读写模式 不公开：0 公共读：1 公共读写：2')

    relate_panels = relationship('Panel', backref='relate_dashboards', secondary='dashboard_panel')
    __table_args__ = (UniqueConstraint('created_app', 'title'),)


if __name__ == '__main__':
    import json
    from sqlalchemy import create_engine, text
    from sqlalchemy.orm import sessionmaker
    engine = create_engine('postgresql+psycopg2://aiops:aiops@192.168.101.79:5432/aiops', echo=True)
    Base.metadata.create_all(engine)

    def writer_data():
        with sessionmaker(bind=engine)() as s:
            panel = Panel(
                title="p1",
                description='面板1',
                search={'a': 'b', 'c': 'd'},
                chart={'type': 'bar', 'size': '10', 'panels': ['tt', 'gg'], 'ee': [{'id': 'a'}, {'id': 'b'}]}
            )
            s.add(panel)
            s.commit()  # 别忘了提交！

            dashboard = Dashboard(
                    title='d1',
                    description='d1',
                )

            s.add(dashboard)
            s.commit()  # 别忘了提交！

            dashboard_panel = DashboardPanel(dashboard_id=dashboard.id, panel_id=panel.id)

            s.add(dashboard_panel)
            s.commit()  # 别忘了提交！

            # 批量添加数据
            products = [
                Panel(
                    title="p1",
                    description='a',
                    search={'aa': 'b', 'cc': 'd'},
                    chart={'type': 'bar', 'size': '10', 'panels': ['tt', 'gg'], 'ee': [{'id': 'ac'}, {'id': 'bc'}]},
                ),
                Panel(
                    title="p2",
                    description='a',
                    search={'ae': 'b', 'c': 'd'},
                    chart={'type': 'bar', 'size': '10', 'panels': ['tt', 'gg'], 'ee': [{'id': 'aa'}, {'id': 'bb'}]},
                ),
                Dashboard(
                    title='d1',
                    description='d1',
                )
            ]
            s.add_all(products)
            s.commit()

    def read():
        with sessionmaker(bind=engine)() as s:
            data = s.query(Panel).filter().all()  # 等于
            # data = s.query(Panel).filter(Panel.chart['type'] == json.dumps(1)).first()  #  等于
            # data = s.query(Panel).filter(Panel.chart['type'].in_([json.dumps('bar')])).all()  # in 列表
            # data = s.query(Panel).filter(Panel.chart['panels'].contains(json.dumps('tt'))).all()  # 包含
            # data = s.query(Panel).filter(Panel.chart['ee'][0]['id'] == json.dumps('a')).all()  # 下标取值

            data = s.query(Panel).filter(Panel.chart['panels'].contains(json.dumps('tt'))).all()  # 包含
            print(data)


    # writer_data()
    read()
